The present invention relates generally to the field of database management and more particularly to access path selection and query processing.
A database management system (DBMS) is designed to allow the definition, creation, querying, updating, and administration of databases, which are an organized or structured collection of data. Databases can use a partition table, which is a type of table using a data organization scheme in which table data is divided across multiple storage objects, called partitions or ranges, according to values in one or more table partitioning key columns of the table. A table partitioning key is an ordered set of one or more columns in a table. The values in the table partitioning key columns determine in which data partition each row belongs. Partitions of data can be added, attached, detached from a partitioned table and multiple data partition ranges may be stored in a table.
Partition tables may have indexed partition tables. An index is an ordered set of pointers to the data or records in a table. Columns in an index are used to identify data rows, or in some cases, blocks of data, are known as keys. A key can be unique or non-unique. An index may be used to ensure uniqueness since a table with a unique index cannot have rows with identical keys. Data within a partition table may be accessed by scanning the entire table sequentially as done in a table scan or by accessing an index on the table to locate specific rows. In some cases, by another scanning method such as scan sharing which provides the ability of a scan to use the buffer pool pages of another scan. Conventionally, a partition table is accessed by one access method.
Query optimization is a function of DBMS. A query is used to manage data, locate, and retrieve desired data from a database. A query optimizer attempts to determine the most efficient way to execute a given query by evaluating possible query plans for access paths into a database. Since database structure are often complex, the desired data can be collected from a database in different ways (e.g., through different data structures or in different orders). Each different path or way of locating and retrieving data from the database is an access path. Each access path usually requires different processing time, which drives different processing costs. The query optimization may consider the possible access paths or may be limited to an optimization level; an optimization cost, an optimization processing time, or other configured limitation so that only a number of alternative access paths with different data structures are evaluated to determine an effective solution that is typically close to the optimal solution.